﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Model;

namespace DAL
{
    public  class TesterInfoDAL
    {
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetListByGuid(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select [tester_sn]
                                  ,[tester_name]
                                  ,[tester_email]
                                  ,[tester_sex]
                                  ,ISNULL(CONVERT(VARCHAR(100), TESTER_BIRTHDATE, 20),'') AS TESTER_BIRTHDATE
                                  ,[tester_education]
                                  ,[tester_enterprise]
                                  ,[tester_mobile]
                                  ,[tester_experience]
                                  ,[tester_application]
                                  ,[tester_matching]
                                  ,[tester_consistencyscore]
	                              ,ISNULL(CONVERT(VARCHAR(100), TESTER_TESTSTARTTIME, 20),'') AS TESTER_TESTSTARTTIME
                                  ,[tester_enterpriseid]
                                  ,ISNULL(CONVERT(VARCHAR(100), TESTER_TESTENDTIME, 20),'') AS TESTER_TESTENDTIME
                                  ,[tester_experienceinner]
                                  ,[tester_dealer]
                                  ,[tester_department]");
            strSql.Append(" FROM ExamDB.dbo.tb_Tester_Info");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where tester_sn='" + strWhere + "'");
            }
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetListByEnterprise(string eid, string activityID)
        {
            if (string.IsNullOrEmpty(eid) || string.IsNullOrEmpty(activityID))
            {
                return null;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select [tester_sn]
                                  ,[tester_name]
                                  ,[tester_email]
                                  ,[tester_sex]
                                  ,ISNULL(CONVERT(VARCHAR(100), TESTER_BIRTHDATE, 20),'') AS TESTER_BIRTHDATE
                                  ,[tester_education]
                                  ,[tester_enterprise]
                                  ,[tester_mobile]
                                  ,[tester_experience]
                                  ,[tester_application]
                                  ,[tester_matching]
                                  ,[tester_consistencyscore]
	                              ,ISNULL(CONVERT(VARCHAR(100), TESTER_TESTSTARTTIME, 20),'') AS TESTER_TESTSTARTTIME
                                  ,[tester_enterpriseid]
                                  ,ISNULL(CONVERT(VARCHAR(100), TESTER_TESTENDTIME, 20),'') AS TESTER_TESTENDTIME
                                  ,[tester_experienceinner]
                                  ,[tester_dealer]
                                  ,[tester_department]");
            strSql.Append(" FROM ExamDB.dbo.tb_Tester_Info");
            if (eid.Trim() != "")
            {
                strSql.Append(" where tester_enterpriseid='" + eid +
                    "' and tb_Tester_Info.tester_sn in (select guid_str from tb_GUID_Number where guid_status = 2 and activity_id="
                    + activityID + ") order by tester_matching desc");
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static string GetUserNameByGUID(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select tester_name from ExamDB.dbo.tb_Tester_Info");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where tester_sn='" + strWhere + "'");
            }
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if ((ds.Tables[0].Rows.Count == 0) || (string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString())))
            {
                return null;
            }
            return ds.Tables[0].Rows[0][0].ToString();
        }

        public static int Delete(string guid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM tb_Tester_Info where tester_sn='" + guid + "'");
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(TesterInfo model)
        {
            int n = Delete(model.Tester_sn);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ExamDB.dbo.tb_Tester_Info(");
            strSql.Append(@"tester_sn,
                            tester_name,
                            tester_email,
                            tester_sex,
                            tester_birthdate,
                            tester_education,
                            tester_enterprise,
                            tester_mobile,
                            tester_experience,
                            tester_application,
                            tester_matching,
                            tester_consistencyscore,
                            tester_teststarttime,
                            tester_testendtime,
                            tester_experienceinner,
                            tester_dealer,
                            tester_department,
                            tester_enterpriseid)");

            strSql.Append(" values (");

            strSql.Append(@"@tester_sn,
                            @tester_name,
                            @tester_email,
                            @tester_sex,
                            @tester_birthdate,
                            @tester_education,
                            @tester_enterprise,
                            @tester_mobile,
                            @tester_experience,
                            @tester_application,
                            @tester_matching,
                            @tester_consistencyscore,
                            @tester_teststarttime,
                            @tester_testendtime,
                            @tester_experienceinner,
                            @tester_dealer,
                            @tester_department,
                            @tester_enterpriseid)");

            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@tester_sn",SqlDbType.NVarChar,255),
					new SqlParameter("@tester_name", SqlDbType.NVarChar,255),
					new SqlParameter("@tester_email", SqlDbType.NVarChar,255),
					new SqlParameter("@tester_sex", SqlDbType.NVarChar,8),
                    new SqlParameter("@tester_birthdate",SqlDbType.DateTime),
                    new SqlParameter("@tester_education", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_enterprise", SqlDbType.NVarChar,255),
                    new SqlParameter("@tester_mobile", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_experience", SqlDbType.NVarChar ,255),
                    new SqlParameter("@tester_application", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_matching",SqlDbType.Float),
                    new SqlParameter("@tester_consistencyscore",SqlDbType.Float),
                    new SqlParameter("@tester_teststarttime",SqlDbType.DateTime),
                    new SqlParameter("@tester_testendtime",SqlDbType.DateTime),
                    new SqlParameter("@tester_experienceinner", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_dealer", SqlDbType.NVarChar,255),
                    new SqlParameter("@tester_department", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_enterpriseid", SqlDbType.Int,4)
                
                                        };
            parameters[0].Value = model.Tester_sn;
            parameters[1].Value = model.Tester_name;
            parameters[2].Value = model.Tester_email;
            parameters[3].Value = model.Tester_sex;
            parameters[4].Value = model.Tester_birthdate;
            parameters[5].Value = model.Tester_education;
            parameters[6].Value = model.Tester_enterprise;
            parameters[7].Value = model.Tester_mobile;
            parameters[8].Value = model.Tester_experience;
            parameters[9].Value = model.Tester_application;
            parameters[10].Value = model.Tester_matching;
            parameters[11].Value = model.Tester_consistencyscore;
            parameters[12].Value = model.Tester_teststarttime;
            parameters[13].Value = model.Tester_testendtime;
            parameters[14].Value = model.Tester_experienceinner;
            parameters[15].Value = model.Tester_dealer;
            parameters[16].Value = model.Tester_department;
            parameters[17].Value = model.Tester_enterpriseid;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static void Update(TesterInfo  model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamDB.dbo.tb_Tester_Info set ");
            strSql.Append("tester_name=@tester_name,");

            strSql.Append("tester_email=@tester_email,");
            strSql.Append("tester_sex=@tester_sex,");
            strSql.Append("tester_birthdate=@tester_birthdate,");
            strSql.Append("tester_education=@tester_education,");
            strSql.Append("tester_enterprise=@tester_enterprise,");
            strSql.Append("tester_mobile=@tester_mobile,");
            strSql.Append("tester_experience=@tester_experience,");
            strSql.Append("tester_application=@tester_application,");
            strSql.Append("tester_matching=@tester_matching,");
            strSql.Append("tester_consistencyscore=@tester_consistencyscore,");
            strSql.Append("tester_teststarttime=@tester_teststarttime");
            strSql.Append("tester_testendtime=@tester_testendtime");
            strSql.Append("tester_experienceinner=@tester_experienceinner");
            strSql.Append("tester_dealer=@tester_dealer");
            strSql.Append("tester_department=@tester_department");
            strSql.Append("tester_enterpriseid=@tester_enterpriseid");
            strSql.Append(" where tester_sn=@tester_sn ");
            SqlParameter[] parameters = {
                    new SqlParameter("@tester_name", SqlDbType.NVarChar,255),                                
				    new SqlParameter("@tester_email", SqlDbType.NVarChar,255),
					new SqlParameter("@tester_sex", SqlDbType.NVarChar,8),
					new SqlParameter("@tester_birthdate", SqlDbType.DateTime),
					new SqlParameter("@tester_education", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_enterprise", SqlDbType.NVarChar,255),
                    new SqlParameter("@tester_mobile", SqlDbType.NVarChar,255),
                    new SqlParameter("@tester_experience", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_application", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_matching", SqlDbType.Float),
                    new SqlParameter("@tester_consistencyscore", SqlDbType.Float),
                    new SqlParameter("@tester_teststarttime", SqlDbType.DateTime),
                    new SqlParameter("@tester_testendtime", SqlDbType.DateTime),
                    new SqlParameter("@tester_experienceinner", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_dealer", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_department", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_enterpriseid", SqlDbType.NVarChar,50),
                    new SqlParameter("@tester_sn", SqlDbType.NVarChar,255)
                                        };
            parameters[0].Value = model.Tester_name;
            parameters[1].Value = model.Tester_email;
            parameters[2].Value = model.Tester_sex;
            parameters[3].Value = model.Tester_birthdate;
            parameters[4].Value = model.Tester_education;
            parameters[5].Value = model.Tester_enterprise;
            parameters[6].Value = model.Tester_mobile;
            parameters[7].Value = model.Tester_experience;
            parameters[8].Value = model.Tester_application;
            parameters[9].Value = model.Tester_matching;
            parameters[10].Value = model.Tester_consistencyscore;
            parameters[11].Value = model.Tester_teststarttime;
            parameters[12].Value = model.Tester_testendtime;
            parameters[13].Value = model.Tester_experienceinner;
            parameters[14].Value = model.Tester_dealer;
            parameters[15].Value = model.Tester_department ;
            parameters[16].Value = model.Tester_enterpriseid;
            parameters[17].Value = model.Tester_sn;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新单个字段
        /// </summary>
        /// <param name="testersn">测试序号</param>
        /// <param name="updatefieldName">更新字段名</param>
        /// <param name="updatefieldValue">更新字段值</param>
        public static int UpdateTesterInfo(string testersn, string updatefieldName, string updatefieldValue)
        {
            if (string.IsNullOrEmpty(testersn))
               return   -1;
            if (string.IsNullOrEmpty(updatefieldName))
                return -1;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamDB.dbo.tb_Tester_Info set  ");
            strSql.Append(updatefieldName+"='"+updatefieldValue);
            strSql.Append("' where tester_sn='");
            strSql.Append(testersn+"'");
            if (DbHelperSQL.Query(strSql.ToString()) != null)
            {
                return 1;
            }
            else
            {
                return -1;
            }
        }
    }
}
